#!/usr/bin/perl -tT

use strict;
use warnings;

use DBI;
use LWP::Simple;
use Geo::METAR;

# Database configuration
my $config_db_type  = "mysql";     # Database type, default: mysql
my $config_db_host  = "localhost"; # Database host, default: localhost
my $config_db_port  = "3306";      # Database port, default: 3306
my $config_db_db    = "flightdb";  # Database database name, default: flightdb
my $config_db_user  = "root";      # Database user, default: root
my $config_db_pass  = "";          # Database password, default: empty string
my $config_db_table = "metar";     # Database table, default: metar

# Which stations to get
my @stations = (
    "EFET",
    "EFHA",
    "EFHF",
    "EFHK",
    "EFIV",
    "EFJO",
    "EFJY",
    "EFKI",
    "EFKA",
    "EFKE",
    "EFKT",
    "EFKK",
    "EFKU",
    "EFKS",
    "EFLP",
    "EFMA",
    "EFMI",
    "EFOU",
    "EFPO",
    "EFRO",
    "EFSA",
    "EFSI",
    "EFSO",
    "EFTP",
    "EFTU",
    "EFUT",
    "EFVA",
    "EFVR"
);

# The base of the URL, 4 letter station identifier and ".TXT" will be added automagically.
my $baseUrl = "http://weather.noaa.gov/pub/data/observations/metar/stations/";

# Try to open database connection. Die if fails.
my $dbh = DBI->connect( "DBI:$config_db_type:$config_db_db:$config_db_host:$config_db_port", $config_db_user, $config_db_pass ) or die( "ERROR: Can't connect to database : $!" )
;

foreach my $station ( @stations )
{
    my $content = get $baseUrl . $station . ".TXT";
    if ( not defined $content )
    {
	print "Error while retrieving " . $baseUrl . $station . ".TXT\n";
	next;
    }
    
    my $metar = new Geo::METAR;
    $metar->metar( $content );
    
    # Examples how to access the METAR data:
    # print $metar->dump;
    # print "Temperature: " . $metar->{"TEMP_C"} . "\n";
    
    # Generate a timestamp from METAR
    my $timestamp = substr($metar->{"METAR"},0,4) . '-' . substr($metar->{"METAR"},2,2) . '-' . substr($metar->{"date_time"},0,2) . ' ' . substr($metar->{"date_time"},2,2) . ':' . substr($metar->{"date_time"},4,2);
    
    # Construct the SQL insertion
    my $query = "INSERT INTO " . $config_db_table . " ( station, timestamp, temperature, dewpoint, visibility, wind_kts, wind_dir ) VALUES ( " . $dbh->quote($station) . ", " . $dbh->quote($timestamp) . ", " . $dbh->quote($metar->{"TEMP_C"}) . ", " . $dbh->quote($metar->{"DEW_C"}) . ", " . $dbh->quote($metar->{"visibility"}) . ", " . $dbh->quote($metar->{"WIND_KTS"}) . ", " . $dbh->quote($metar->{"WIND_DIR_DEG"}) . ");";
    
    print $query . "\n";
    
    # Execute the SQL statement
    $dbh->prepare($query)->execute;
}
